// ==========================================================================
// 所属模块：lib - sql
// 生成日期：2024-02-19 00:24:00 +0800 CST
// 生成路径: lib/sql.js
// 生成人：Cato
// 功能描述：定义全局通用的SQL的CURD功能封装，包含分页列表和模版查询、数据增删等等。（注册于global.js内)
// ==========================================================================
// 系统的sql数据库连接名为 DB_NAME

// 连接测试数据库
// var test_sql = SQL.new(
//     'mydb',
//     'mysql',
//     'root:123456@tcp(127.0.0.1:3306)/test_db'
// )

function selectData(condition) {
	const newPlaceholders = getPlaceholders(condition).placeholders;
	if (newPlaceholders) {
		const tempParams = getPlaceholders(condition).tempParams;
		return {
			query: `SELECT ${TABLE_FIELDS ? TABLE_FIELDS : '*'} FROM ${TABLE_NAME} WHERE ${newPlaceholders} AND delete_time IS NULL LIMIT ?,?`,
			params: [...tempParams, (condition.pageNum - 1) * condition.pageSize, condition.pageSize],
		};
	} else {
		return {
			query: `SELECT ${TABLE_FIELDS ? TABLE_FIELDS : '*'} FROM ${TABLE_NAME} WHERE delete_time IS NULL LIMIT ?,?`,
			params: [(condition.pageNum - 1) * condition.pageSize, condition.pageSize],
		};
	}
}

function countTotal(condition) {
	const newPlaceholders = getPlaceholders(condition).placeholders;
	const tempParams = getPlaceholders(condition).tempParams;
	if (newPlaceholders) {
		return {
			query: `SELECT COUNT(*) AS count FROM ${TABLE_NAME} WHERE ${newPlaceholders} AND delete_time IS NULL `,
			params: [...tempParams],
		};
	} else {
		return {
			query: `SELECT COUNT(*) AS count FROM ${TABLE_NAME} WHERE delete_time IS NULL`,
			params: [],
		};
	}
}

function sql_select() {
	const data = payload.get().query;
	if (!data.pageSize || !data.pageNum) {
		// 根据id查询详情
		var useParent = scriptPath != payload.get().path.substring(1);
		if (useParent) {
			let value = payload.get().path.split('/').pop();
			const result = SQL.query(
				DB_NAME,
				`SELECT * FROM ${TABLE_NAME} WHERE delete_time IS NULL AND ${COLUMN_KEY} = ?`,
				value
			);

			if (result.length) {
				const newResult = {};
				Object.keys(result[0]).forEach((item) => {
					newResult[underscoreToCamel(item)] = result[0][item];
				});
				return okMsg(newResult);
			}
			return errMsg(507, '获取失败');
		}
	}
	// 格式化这个query参数
	let newData = {};
	Object.keys(data).forEach((item) => {
		newData[item] = data[item][0];
	});

	// 总数据
	const ROWS_COUNT = countTotal(newData);
	const count = SQL.query(DB_NAME, ROWS_COUNT.query, ...ROWS_COUNT.params);
	// 分页数据
	const LIST_DATA = selectData(newData);
	const result = SQL.query(DB_NAME, LIST_DATA.query, ...LIST_DATA.params);
	// 格式化数据
	const results = result.map((row) => {
		const result = {};
		Object.keys(row).forEach((col) => {
			result[underscoreToCamel(col)] = row[col];
		});
		return result;
	});

	return okMsg({
		data: results,
		count: Number(count[0]?.count),
		pageNum: Number(newData.pageNum),
		pageSize: Number(newData.pageSize),
	});
}

function sql_update() {
	const data = payload.get().body.toObject();
	const newData = {};
	Object.keys(data).forEach((item) => {
		newData[camelToUnderscore(item)] = data[item];
	});
	let filter = payload.get().path.split('/').pop();
	const columnsArr = Object.keys(newData);
	let dataArr = [];
	const placeholders = [];
	columnsArr.forEach((item) => {
		if (newData[item]) {
			placeholders.push(`${item} = ?`);
			dataArr.push(newData[item]);
		} else {
			placeholders.push(`${item} = NULL`);
		}
	});
	const newPlaceholders = placeholders.join(',');
	const query = `UPDATE ${TABLE_NAME} SET ${newPlaceholders}, update_time = NOW() WHERE ${COLUMN_KEY} = ?`;

	const res = SQL.exec(DB_NAME, query, ...[...dataArr, filter]);
	if (res) {
		return okMsg(res);
	}
	return errMsg(507, '更新失败');
}

function sql_insert() {
	const data = payload.get().body.toObject();
	const newData = {};
	Object.keys(data).forEach((item) => {
		newData[camelToUnderscore(item)] = data[item];
	});
	const columnsArr = Object.keys(newData);
	let dataArr = Object.values(newData);
	const placeholders = [];

	columnsArr.push(COLUMN_KEY)
	columnsArr.push('proprietor_id')
	columnsArr.push('proprietor_name')

	dataArr.push(ex.suid().base58())
	dataArr.push(USER_INFO.user_id)
	dataArr.push(USER_INFO.nickname)

	dataArr.forEach(() => {
		placeholders.push('?');
	});
	placeholders.join(',');

	const columnsStr = columnsArr.join(',');
	const query = `INSERT INTO ${TABLE_NAME} (${columnsStr}) VALUES (${placeholders})`;
	const res = SQL.exec(DB_NAME, query, ...dataArr);

	if (res) {
		return okMsg(res);
	}
	return errMsg(507, '添加失败');
}

function sql_delete() {
	let filter = payload.get().path.split('/').pop();
	let query;
	let params = filter.split(',');
	// 兼容批量删除
	const tables = params.map((item) => '?');
	query = `UPDATE ${TABLE_NAME} SET delete_time = NOW() WHERE ${COLUMN_KEY} IN (${tables.join(',')});`;
	const res = SQL.exec(DB_NAME, query, ...params);
	if (res) {
		return okMsg(res);
	}
	return errMsg(507, '删除失败');
}

// 基本的CURD功能
function base_curd() {
	switch (payload.get().method) {
		case 'GET':
			return sql_select();
		case 'PUT':
			return sql_update();
		case 'POST':
			return sql_insert();
		case 'DELETE':
			return sql_delete();
		default:
			response.status.notFound();
			return `404 page not found`;
	}
}
